-- gets the "latest" document key given a DocumentKey or a DocumentVersionKey.  If key is DocumentKey
-- it looks at all versions of that Document (same DocumentVersionKey)  If key is a DocumentVersionKey,
-- returns the DocumentKey of the document with the lowest status (e.g., prefers working over published,
-- published over archived, etc.) and the most recent date
CREATE PROCEDURE [dbo].[asi_DocumentGetLatestVersionDocumentKey] @key uniqueidentifier, @documentKey uniqueidentifier OUT AS
BEGIN
   SELECT @documentKey = a.DocumentKey
     FROM DocumentMain a inner join DocumentMain b on a.DocumentVersionKey = b.DocumentVersionKey
    WHERE (b.DocumentKey = @key OR b.DocumentVersionKey = @key)
      AND a.CreatedOn = (
          SELECT TOP 1 CreatedOn
            FROM DocumentMain
           WHERE DocumentVersionKey = a.DocumentVersionKey
           ORDER BY DocumentStatusCode, CreatedOn DESC)
END

GO